<?php
wp_enqueue_script('jquery-ui-datepicker');
//wp_enqueue_style('jquery-style', 'http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.2/themes/smoothness/jquery-ui.css');

?> 
<link rel="stylesheet" type="text/css" href="<?php echo get_template_directory_uri(); ?>/css/jquery-ui.css">
<style type="text/css">
.form_control { width:99%; min-height:35px; border:1px solid #ccc; padding:0px 10px; border-radius:4px;}
.btnFull { min-height:38px; width:96%; margin:0 2%; line-height:36px;}
</style>

<script type="text/javascript">
	/* <![CDATA[ */
	jQuery(document).ready(function(){
		jQuery('#memberlist').dataTable();
	});
	/* ]]> */
	
	jQuery(document).ready(function() {
    jQuery('#to_date').datepicker({
        dateFormat : 'dd-mm-yy'
    });
	 jQuery('#from_date').datepicker({
        dateFormat : 'dd-mm-yy'
    });
});

</script>
<?php
	
	require_once("userclass.php");
	$objUser = new userClass();
	
	require_once("paymentclass.php");
	$objPayment = new paymentClass();
	
	require_once("trackclass.php");
	$objTrack = new trackClass();
	
	global $wpdb;
	
	
	if($_REQUEST['report'] == 'yes'){
	
	
	
		echo '<script>
						 window.open("'.get_site_url().'/XLS/report.csv","_blank"); 
						 window.location.href = "'.get_site_url().'/wp-admin/admin.php?page=reports"; 
					</script>';
					
	}
	
?>




<?php 



if(isset($_POST['save_student_details'])){
			$reg_no = array();
			$reg_no = $_POST['class'];
			
			$pass_out = array();
			$pass_out = $_POST['pass_out'] ? $_POST['pass_out'] : '';
			
			
			foreach($reg_no as $key=>$val){
			
					$cont = mysql_query("SELECT COUNT(id) AS count FROM catchism_students WHERE parishioner_id = ".$key.";");
				$row  = mysql_fetch_array($cont);
				if($row['count']==0)
						mysql_query("INSERT INTO `catchism_students` (`id`, `parishioner_id`, `class`,`pass_out`) VALUES (NULL, '".$key."', '".$val."','".$pass_out[$key]."');");
				else{
					mysql_query('UPDATE  `catchism_students` SET  `class` =  '.$val.' ,`pass_out` =  '.$pass_out[$key].'  WHERE  `catchism_students`.`parishioner_id` ='.$key.';');
					}
			}
}			
			?>

<div class="wrap">
    <h2>Famlily Report&nbsp;&nbsp;</h2>
    <form action="" method="post">
  
              <table class="" width="100%" border="0" cellspacing="0" cellpadding="0"  bgcolor="#666666" style="color:#FFF">
  <tbody>
    <tr>
      <td width="20%"  style="padding-left:5px;">Unit</td>
      <td width="20%">Assign To</td>
      <td width="20%">Order Status </td>
      <td width="24%"><table width="100%" border=" 0" cellspacing="5" cellpadding="0">
          <tbody>
            <tr>
              <td>Default(Order Date)</td>
               
            </tr>
          </tbody>
      </table></td>
      <td width="10%">&nbsp;</td>
      <td width="10%">&nbsp;</td>
    </tr>
    <tr>
      <td style="padding-left:5px;"><select name="unit" class="form_control" ><option value="">Select</option>
                					 <option <?php echo (($_POST['payment_status']== 1) ? 'selected="selected"' : '');?> value="1">St Antony</option>		
                					 <option <?php echo (($_POST['payment_status']== 2) ? 'selected="selected"' : '');?> value="2">St Peter</option>	
                                     <option <?php echo (($_POST['payment_status']== 3) ? 'selected="selected"' : '');?> value="3">St james</option>	
                                     <option <?php echo (($_POST['payment_status']== 4) ? 'selected="selected"' : '');?> value="4">Mariyam Thresia</option>	
									 <option <?php echo (($_POST['payment_status']== 5) ? 'selected="selected"' : '');?> value="5">St Thomas</option>	
									  <option <?php echo (($_POST['payment_status']== 6) ? 'selected="selected"' : '');?> value="6">Bl Evuprasiama</option
									 ><option <?php echo (($_POST['payment_status']== 7) ? 'selected="selected"' : '');?> value="7">St Sebastian</option>	 						                                     <option <?php echo (($_POST['payment_status']== 8) ? 'selected="selected"' : '');?> value="8">St Alphonsa</option>	 
									 <option <?php echo (($_POST['payment_status']== 9) ? 'selected="selected"' : '');?> value="9">St Joseph</option>	 
										 
								
                                                      
                        			</select></td>
      <td><select name="user" class="form_control" >
                            <option value="">User List</option>
                            <?php
                            echo $objUser->listAllUsers($_POST['user']);
                            ?>
                           </select></td>
      <td><select name="track_status" class="form_control" >3
                            <option value="">Select Status</option>
                            <?php echo $objTrack->getTrackSelect($_POST['track_status']); ?>
                           </select></td>
      <td><table width="100%" border="0" cellspacing="5" cellpadding="0">
        <tbody>
          <tr>
            <td><input type="text" id="from_date" name="from_date" value="<?php echo $_POST['from_date']; ?>" placeholder="From Date" class="form_control" >
				  </td>
            <td><input type="text" id="to_date" name="to_date" value="<?php echo $_POST['to_date']; ?>" placeholder="To Date" class="form_control" ></td>
          </tr>
        </tbody>
      </table></td>
      <td><a href="admin.php?page=reports&report=yes&user=<?php echo $id; ?>"></a> <input type="submit" value="Search" class="button button-primary btnFull" name="serch_val" />    </td>
      <td style="padding-right:10px;"><u class="btnFull" style="">
	  
	  <?php 	if($_POST['serch_val']){ ?>
	  <a style="color:#FFF; margin-top:15px; text-decoration:none ;float:right;" href="<?php echo get_site_url().'/XLS/report.csv';?>" ><img src="<?php echo get_template_directory_uri();?>/images/export_excel.png"/></a>
	  
	  <?php } ?>
	  
	  <!--href="admin.php?page=reports&report=yes&user=<?php echo $id; ?>" -->
	
	  </u></td>
    </tr>
  </tbody>
</table>
          
	 <table width="100%" class="wp-list-table widefat fixed " id="memberlist">
		<thead>
			<tr>
				<th width="50"><u> ID</u></th>
				<th width="50"><u> DB</u></th>
				<th><u>House</u></th>
				<th width="70"><u>Family Head</u></th>
				
                <th><u>Family No</u></th>
				<th><u>Unit</u></th>
				<th><u>Members</u></th>
				<th><u>Notes</u></th>
				
				<th>View Details</th>
			</tr>
		</thead>
		<tbody>
		<script type="text/javascript">
				/* <![CDATA[ */
				jQuery(document).ready(function(){
					jQuery('#mytable').dataTable();
				});
				/* ]]> */

			</script>
<?php
		if($_REQUEST['user']!='')
			$addon1 = ' AND   wp_spec_assigned_orders.assigned_to ='.$_REQUEST['user'].'';
		 if($_REQUEST['payment_status']!='')	
		    $addon2 = ' AND  wp_spec_checkout.payment_status='.$_REQUEST['payment_status'].'';
		 if($_REQUEST['track_status']!=0){
			   if($_POST['track_status']==8)
						$addon3= ' AND wp_spec_order_trac.track_status != 7 AND wp_spec_order_trac.track_status != 6';
				else		
						$addon3 = ' AND wp_spec_order_trac.track_status='.$_REQUEST['track_status'].'';	
			}
		 if(($_REQUEST['from_date']!='') && ($_REQUEST['to_date']!='')){
			
			
			# FROM DATE
			$from_date		 = $_REQUEST['from_date'];
			$from_date_array = explode("-", $from_date);
			$date  =  $from_date_array[0]; 
			$month =  $from_date_array[1]; 
			$year  =  $from_date_array[2]; 
			$fromdate = mktime(0,0,0,$month,$date,$year);
			
			
			# TO DATE
			$to_date		 = $_REQUEST['to_date'];
			$to_date_array = explode("-", $to_date);
			$date  =  $to_date_array[0]; 
			$month =  $to_date_array[1]; 
			$year  =  $to_date_array[2]; 
			$todate = mktime(0,0,0,$month,$date,$year);		
		
	
				$addon4 = 'AND wp_spec_checkout.payment_time  BETWEEN '.$fromdate.' AND '.$todate.'';
			}
			
			if($_REQUEST['unit']!='')	
				$addon1 = ' AND   parish_ward.ward_no ='.$_REQUEST['unit'].'';	
		
	 	
	/*	 $sql=	  "SELECT parishioner.id, parishioner.name, parishioner.date_of_birth, 
						parish_ward.ward_name,parish_ward.ward_no, 
						catchism_students.class,catchism_students.pass_out,
						family.id AS family_id 
						FROM parish_ward 
						INNER JOIN family ON  parish_ward.ward_no = family.ward_no
						INNER JOIN parishioner ON  family.id= parishioner.family_id 
						LEFT JOIN catchism_students ON parishioner.id=catchism_students.parishioner_id
						WHERE  1 $addon1
						GROUP BY parishioner.id";*/
						
		$sql = "SELECT 
                		family.id,family.family_name,family.family_no,family.head_person,
						family.ward_no,family.notes,
						parish_ward.ward_name	,
						(SELECT COUNT( parishioner.id ) 
								FROM parishioner
								WHERE parishioner.family_id = family.id) AS members_count		
					  FROM family
					  INNER JOIN parish_ward ON  family.ward_no = parish_ward.ward_no
					  WHERE  1 $addon1"; 								
		
					
		$result = mysql_query($sql) or die ('Error, query failed');
		if (mysql_num_rows($result) > 0 )
		{
		?>
				
<?php		
			$content[]= array(); 
			$content[]= array('ID','DB','HOSUE NO','HOSUE NAME','FAMILY HEAD','UNIT','TOTAL MEMBERS');
			$i=1;
			while ($row = mysql_fetch_assoc($result))
			{
				$id		 		 = $row['id'];
				$family_name	 = $row['family_name'];
				$head_person	 = $row['head_person'];
				$family_no	 	 = $row['family_no'];
				$ward_name  	 = $row['ward_name'];
				$family_id		 = $row['id'];	
				$notes  		 = $row['notes'];
				$members_count	 = $row['members_count'];
				$content[]= array($i,$family_no,'',$family_name,$head_person,$ward_name,$members_count,$notes);
				
				# GET FAMILY DETAILS
			 	$sql_fam_details = "SELECT * FROM parishioner WHERE   family_id=$family_id"; 		
				$result_fly = mysql_query($sql_fam_details) or die ('Error, query failed');
				mysql_num_rows($result_fly);
				if (mysql_num_rows($result_fly) > 0 )
					{	
						$content[]= array('','','','NAME','STUDYING CLASS');
						while ($row_fly = mysql_fetch_assoc($result_fly))
						{	$id       		 = $row_fly['id'];
							$name  			 = $row_fly['name'];
							$e_mail          = $row_fly['e_mail'];
							$date_of_birth     = $row_fly['date_of_birth'];
							$baptismal_name     = $row_fly['baptismal_name'];
							$phone_no		    = $row_fly['phone_no'];
							$mob_no		    	= $row_fly['mob_no'];
							$blood_group	  	= $row_fly['blood_group'];
							$education	  		= $row_fly['education'];
							$occupation	  	    = $row_fly['occupation'];
							
							$content[]= array('','','',$name,'','','');
						}
				}	
								
			    # END		
				
				
	?>
			<tr>
				<td><input type="hidden" value="<?php echo $id; ?>" name="parishioner[<?php echo $id; ?>]" /><?php echo $i; ?></td>
					<td nowrap><?php echo $family_no; ?></td>
				<td nowrap><?php echo $family_name; ?></td>
				<td nowrap><?php echo $head_person; ?></td>
		        <td nowrap></td>
				<td nowrap><?php echo $ward_name; ?></td>
				<td nowrap>
				<a href="admin.php?page=memberlist.php&act=upd&family_id=<?php echo $id; ?>"><?php  echo $members_count; ?></a>
				</td>
				<td nowrap><?php echo $notes;?></td>				
			
                
				
				<td width="10%" nowrap><u style="background-color:  #0074A2; text-align:center;color:#FFF; border: 2px solid; min-width:50px; display:block;
    border-radius: 50px;text-decoration:none;" ><a style="color:#FFF; text-decoration:none" href="admin.php?page=order_more_info&act=upd&id=<?php echo $id; ?>">View Details</a></u>
</td>
			</tr>
			
<?php $i++;}
		if($_POST['serch_val']){
			$file_name = 'report';
			$path = ''.get_home_path().'XLS/'.$file_name.'.csv';
			if(file_exists($path)) {
			unlink($path);
			}
			$list = $content;
			$fp = fopen($path, 'w');
			foreach ($list as $fields) {
				fputcsv($fp, $fields);
			}
			fclose($fp);
		}	
	} else { ?>
			<tr>
				<td>No Record Found!</td>
				<td nowrap></td>
				<td nowrap></td>
				<td nowrap></td>
                <td nowrap></td>
				<td nowrap></td>
				<td nowrap></td>
				<td nowrap></td>
				<td nowrap></td>
				<td width="10%" nowrap>
</td>
			</tr>
			
	<?php } ?>
	
	
	</tbody>
	</table>
		<table>
	<tr><td > <input type="submit" value="Save Data" name="save_student_details" /></td><td nowrap></td>
				<td nowrap></td>
				<td nowrap></td><td nowrap></td>
                <td nowrap></td>
				<td nowrap></td>
				<td nowrap></td>
				<td nowrap></td>
				<td nowrap></td>
				<td nowrap></td>
				<td nowrap></td>
				<td nowrap></td></tr>
				</table>
				 </form>
</div>
